*Importing national QWI data from Census Website
insheet using "$SourceData\national QWI.csv", clear
drop if firmsize == 0
gen sm_f_emp = emp if firmsize<=3
gen lg_f_emp = emp if firmsize ==5

sort year
keep if year<=2017
collapse (sum) sm lg, by(year)

save "$LocalData\NationalFirmSize.dta", replace

*Importing CPI for use in small/large designation by banks for national aggregates
use "$LocalData\CPI.dta", clear
gen base = cpaltt01usa661s[12]
replace cpaltt01usa661s = cpaltt01usa661s/base

preserve
*	insheet using "$SourceData\SOD_Data.csv", clear
	insheet using "$SourceData\SOD_Data.csv", clear
	sort year
	save "$LocalData\temp.dta", replace
restore

merge 1:m year using "$LocalData\temp.dta"

destring asset depsumbr, ignore(",") replace
	replace asset = asset/cpaltt01usa661s

	* Drop if there is no asset data.
	drop if missing(asset)

	* Banks are "big" if they have more than $50 billion in assets"
	gen byte big = asset>50000000
	label var big "Assets > $50 billion"

	* Banks are "small" if they have more than $1 billion in assets"
	gen small = asset<1000000

	gen sm_dep_year = depsumbr if small==1
	gen lg_dep_year = depsumbr if big==1

collapse (sum) sm_dep lg_dep, by (year)

merge 1:1 year using "$LocalData\NationalFirmSize.dta"

gen sm_dep_year2000 = sm_dep_year[3]
gen lg_dep_year2000 = lg_dep_year[3]
gen sm_f_emp2000 = sm_f_emp[3]
gen lg_f_emp2000 = lg_f_emp[3]

gen Small_Firm_Emp_Growth = sm_f_emp/sm_f_emp2000
gen Large_Firm_Emp_Growth = lg_f_emp/lg_f_emp2000
gen Small_Bank_Dep_Growth = sm_dep_year/sm_dep_year2000
gen Large_Bank_Dep_Growth = lg_dep_year/lg_dep_year2000


export delimited "$LocalData\Results\Figure1bc.csv", replace

	erase "$LocalData\temp.dta"
	erase "$LocalData\NationalFirmSize.dta"
***************************************************************************



################################################################################
# What? Assemble receipts data from SUSB

# SUSB sources:
# https://www2.census.gov/programs-surveys/susb/tables/time-series/
#
# Figure 1a
################################################################################

library(readxl)
library(data.table)
library(ggplot2)
library(haven)
library(tidyverse)

loc<-"/dir/SourceData"
out_loc<-"/dir/Results"

################################################################################
# 2007 to 2020
################################################################################

import excel "$SourceData\us_state_totals_2007-2020.xlsx", firstrow sheet("2007") cellrange(B1:J10) clear 
gen small = RECEIPTS if substr(ENTERPRISE,1,1)=="8"
gen vsmall = RECEIPTS if substr(ENTERPRISE,1,1)>="5" & substr(ENTERPRISE,1,1)<="6"
gen large = RECEIPTS if substr(ENTERPRISE,1,1)=="9"
collapse (sum) small vsmall large 
gen year = 2007
save "$LocalData\temp.dta", replace

import excel "$SourceData\us_state_totals_2007-2020.xlsx", firstrow sheet("2012") clear cellrange(B1:J10)
gen small = RECEIPTS if substr(ENTERPRISE,1,2)=="08"
gen vsmall = RECEIPTS if substr(ENTERPRISE,1,2)>="05" & substr(ENTERPRISE,1,2)<="06"
gen large = RECEIPTS if substr(ENTERPRISE,1,2)=="09"
collapse (sum) small vsmall large 
gen year = 2012
append using "$LocalData\temp.dta"
save "$LocalData\temp.dta", replace

import excel "$SourceData\us_state_totals_2007-2020.xlsx", firstrow sheet("2017") clear cellrange(B3:J13)
gen small = Rec if substr(Enterprise,1,2)=="08"
gen vsmall = Rec if substr(Enterprise,1,2)>="05" & substr(Enterprise,1,2)<="06"
gen large = Rec if substr(Enterprise,1,2)=="09"
collapse (sum) small vsmall large 
gen year = 2017
append using "$LocalData\temp.dta"
save "$LocalData\temp.dta", replace


import excel "$SourceData\us_state_totals_1988-2006.xlsx", firstrow sheet("1997")cellrange(A9:J268) clear
*adding employee buckes of 1-499 employees
gen vsmall = D+E+F+G+H
gen small = vsmall+I
gen large = J
gen year = 1997
keep if _n==5
keep vsmall small large year
save "$LocalData\temp2.dta", replace

import excel "$SourceData\us_state_totals_1988-2006.xlsx", firstrow sheet("2002")cellrange(A9:J268) clear
*adding employee buckes of 1-499 employees
gen vsmall = D+E+F+G+H
gen small = vsmall+I
gen large = J
gen year = 2002
keep if _n==4
keep vsmall small large year
append using "$LocalData\temp2.dta"
append using "$LocalData\temp.dta"
sort year

gen vsmall_norm = vsmall/vsmall[1]
gen small_norm = small/small[1]
gen large_norm = large/large[1]

export delimited "$LocalData\Results\nation_rec_growth.csv"
